package com.ngocvo.QuanLyThuChi;

import java.util.ArrayList;
import java.util.Date;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class CashFlowData extends SQLiteOpenHelper{
	
	final String CASHFLOW_TABLE_NAME = "tbl_CashFlow";
	final String ID = "_id";	
	final String AMOUNT = "Amount";
	final String NAME = "Name";
	final String FLOW_TYPE = "FlowType";
	final String STATUS = "Status";
	final String CREATE_DATE = "CreateDate";
	final String MODIFIED_DATE = "ModifiedDate";
	final String FLOW_DATE = "FlowtDate";
	final String FLOW_BACK_DATE = "FlowBackDate";
	
	public CashFlowData(Context context) {
		super(context, Global.DATABASE_NAME, null, Global.DATABASE_VER);		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String strCreateCashFlowQuery = "CREATE TABLE " + CASHFLOW_TABLE_NAME + "(" +
										ID + " BIGINT PRIMARY KEY, " +										
										AMOUNT + " BIGINT NOT NULL, " +
										NAME + " TEXT NOT NULL, " +
										FLOW_TYPE + " INTEGER NOT NULL, " +
										STATUS + " SMALLINT, " +
										CREATE_DATE + " TEXT NOT NULL, " +
										MODIFIED_DATE + " TEXT NOT NULL, " +
										FLOW_DATE + " TEXT NOT NULL, " +
										FLOW_BACK_DATE + " TEXT)";
		
		db.execSQL(strCreateCashFlowQuery);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXIST " + CASHFLOW_TABLE_NAME);
		onCreate(db);
	}
	
	public void Add(CashFlow cashFlow) throws
		Exception{
		SQLiteDatabase dbLite = this.getWritableDatabase();
		try{
						
			ContentValues cv = new ContentValues();
			cv.put(ID, cashFlow.getId());
			cv.put(AMOUNT, cashFlow.getAmount());
			cv.put(NAME, cashFlow.getName());
			cv.put(FLOW_TYPE, cashFlow.getFlowType());
			cv.put(STATUS, cashFlow.getStatus());							
			cv.put(CREATE_DATE, Global.dbFormat.format(cashFlow.getCreateDate()));
			cv.put(MODIFIED_DATE, Global.dbFormat.format(cashFlow.getModifiedDate()));
			cv.put(FLOW_DATE, Global.dbFormat.format(cashFlow.getFlowDate()));
			if(cashFlow.getFlowBackDate()==null){
				cv.put(FLOW_BACK_DATE, "");
			}else{
				cv.put(FLOW_BACK_DATE, Global.dbFormat.format(cashFlow.getFlowBackDate()));	
			}					
			dbLite.insert(CASHFLOW_TABLE_NAME, ID, cv);
			
		}finally{
			dbLite.close();
		}
	}
	
	public void Delete(int id)throws
		Exception{
		SQLiteDatabase dbLite = this.getWritableDatabase();
		Cursor c = dbLite.rawQuery("SELECT COUNT(ID) FROM " + CASHFLOW_TABLE_NAME + " WHERE " + ID + " = " + id, null);
		try{
			if(c.getInt(0)<=0){
				throw new Exception("The item does not exist in database.");
			}
			else{
				dbLite.delete(CASHFLOW_TABLE_NAME, ID + "=?", new String[]{Integer.toString(id)});				
			}
		}finally{
			dbLite.close();
		}
	}
	
	public void Update(CashFlow cashFlow)throws
		Exception{		
		String strQuery = "SELECT " + ID + " FROM " + CASHFLOW_TABLE_NAME + " WHERE " + ID + "=?";
		SQLiteDatabase dbLite = this.getWritableDatabase();		
		try{
			Cursor c = dbLite.rawQuery(strQuery, new String[]{Long.toString(cashFlow.getId())});
			if(c.moveToFirst()){
				
				ContentValues cv = new ContentValues();
				cv.put(ID, cashFlow.getId());
				cv.put(AMOUNT, cashFlow.getAmount());
				cv.put(NAME, cashFlow.getName());
				cv.put(FLOW_TYPE, cashFlow.getFlowType());
				cv.put(STATUS, cashFlow.getStatus());
				cv.put(CREATE_DATE, Global.dbFormat.format(cashFlow.getCreateDate()));
				cv.put(MODIFIED_DATE, Global.dbFormat.format(cashFlow.getModifiedDate()));
				cv.put(FLOW_DATE, Global.dbFormat.format(cashFlow.getFlowDate()));
				if(cashFlow.getFlowBackDate()==null){
					cv.put(FLOW_BACK_DATE,"");
				}else{
					cv.put(FLOW_BACK_DATE, Global.dbFormat.format(cashFlow.getFlowBackDate()));
				}				
							
				dbLite.update(CASHFLOW_TABLE_NAME, cv, ID + "=?", new String[]{Long.toString(cashFlow.getId())});
			}
			else{
				throw new Exception("The item does not exist in database.");
			}
		}finally{
			dbLite.close();
		}
	}
	public Cursor getAll()throws
		Exception{
		String strQuery = "SELECT * FROM " + CASHFLOW_TABLE_NAME;
		SQLiteDatabase dbLite = this.getReadableDatabase();
		Cursor c = dbLite.rawQuery(strQuery, null);
		return c;
	}
	
	public ArrayList<CashFlow> getAllByFlowType(int flowType)throws
		Exception{		
		String strQuery = "SELECT " + ID + ", " + NAME + ", " + AMOUNT + ", " + FLOW_DATE + ", " + STATUS + 
							" FROM " + CASHFLOW_TABLE_NAME 
							+ " WHERE " + FLOW_TYPE + " = " + String.valueOf(flowType) + " ORDER BY " + FLOW_DATE + " DESC";		
		SQLiteDatabase dbLite = this.getReadableDatabase();		
		ArrayList<CashFlow> cashList = new ArrayList<CashFlow>();
		try{			
			Cursor c = dbLite.rawQuery(strQuery, null);
			if(c.moveToFirst()){				
				while(!c.isAfterLast()){					
					CashFlow cashFlow = new CashFlow();
					cashFlow.setId(c.getLong(c.getColumnIndex(ID)));
					cashFlow.setName(c.getString(c.getColumnIndex(NAME)));
					cashFlow.setAmount(c.getInt(c.getColumnIndex(AMOUNT)));
					cashFlow.setFlowDate(Global.dbFormat.parse(c.getString(c.getColumnIndex(FLOW_DATE))));
					cashFlow.setStatus(c.getShort(c.getColumnIndex(STATUS)));
					cashList.add(cashFlow);
					c.moveToNext();
				}			
			}
		}finally{
			dbLite.close();
		}

		return cashList;
	}
	
	public CashFlow Get(long id) throws
	Exception{
	String strQuery = "SELECT * FROM " + CASHFLOW_TABLE_NAME + " WHERE " + ID + "=?";
	CashFlow cashFlow = new CashFlow();
	SQLiteDatabase dbLite = this.getReadableDatabase();
	try{
		Cursor c = dbLite.rawQuery(strQuery, new String[]{Long.toString(id)});
		if(c.moveToFirst()){
			cashFlow.setId(c.getLong(c.getColumnIndex(ID)));
			cashFlow.setAmount(c.getInt(c.getColumnIndex(AMOUNT)));
			cashFlow.setName(c.getString(c.getColumnIndex(NAME)));
			cashFlow.setFlowType(c.getInt(c.getColumnIndex(FLOW_TYPE)));
			cashFlow.setStatus(c.getShort(c.getColumnIndex(STATUS)));
			cashFlow.setCreateDate(Global.dbFormat.parse(c.getString(c.getColumnIndex(CREATE_DATE))));
			cashFlow.setModifiedDate(Global.dbFormat.parse(c.getString(c.getColumnIndex(MODIFIED_DATE))));
			cashFlow.setFlowDate(Global.dbFormat.parse(c.getString(c.getColumnIndex(FLOW_DATE))));
			if(c.getString(c.getColumnIndex(FLOW_BACK_DATE)).equals("")){
				Date date = null;
				cashFlow.setFlowBackDate(date);
			}else{
				cashFlow.setFlowBackDate(Global.dbFormat.parse(c.getString(c.getColumnIndex(FLOW_BACK_DATE))));
			}
			
		}
	}finally{
		dbLite.close();			
	}
	return cashFlow;
}
	
	public long getTotal(int type)throws
		Exception{
		SQLiteDatabase dbLite = this.getReadableDatabase();
		String[] strType = new String[]{Integer.toString(type)};
		long totalIncome = 0;
		String strQuery = "SELECT SUM(" + AMOUNT + ") AS TotalIncome FROM " + CASHFLOW_TABLE_NAME + " WHERE " + FLOW_TYPE + "=?";
		try{
			Cursor c = dbLite.rawQuery(strQuery, strType);
			if(c.moveToFirst()){
				totalIncome = c.getLong(c.getColumnIndex("TotalIncome"));
			}			
		}finally{
			dbLite.close();
		}
		return totalIncome;
	}
}
